package com.example.demo.repository;

import com.example.demo.model.ProductionTracking;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class ProductionTrackingDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public ProductionTrackingDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public int insertProductionTracking(ProductionTracking productionTracking) {
        String sql = "insert into ProductionTracking(product_name, planned_quantity, department, manager_id, completed_quantity, audit_status, plan_id, product_id, staff_id, batch_number, remarks) values(?,?,?,?,?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql,
                productionTracking.getProduct_name(),
                productionTracking.getPlanned_quantity(),
                productionTracking.getDepartment(),
                productionTracking.getManager_id(),
                productionTracking.getCompleted_quantity(),
                productionTracking.getAudit_status(),
                productionTracking.getPlan_id(),
                productionTracking.getProduct_id(),
                productionTracking.getStaff_id(),
                productionTracking.getBatch_number(),
                productionTracking.getRemarks());
    }

    public int updateProductionTracking(ProductionTracking productionTracking) {
        String sql = "update ProductionTracking set product_name = ?, planned_quantity = ?, department = ?, manager_id = ?, completed_quantity = ?, audit_status = ?, plan_id = ?, product_id = ?, staff_id = ?, batch_number = ?, remarks = ? where id = ?";
        return jdbcTemplate.update(sql,
                productionTracking.getProduct_name(),
                productionTracking.getPlanned_quantity(),
                productionTracking.getDepartment(),
                productionTracking.getManager_id(),
                productionTracking.getCompleted_quantity(),
                productionTracking.getAudit_status(),
                productionTracking.getPlan_id(),
                productionTracking.getProduct_id(),
                productionTracking.getStaff_id(),
                productionTracking.getBatch_number(),
                productionTracking.getRemarks(),
                productionTracking.getId());
    }

    public int deleteProductionTracking(int id) {
        String sql = "delete from ProductionTracking where id = ?";
        return jdbcTemplate.update(sql, id);
    }

    public ProductionTracking getProductionTracking(int id) {
        String sql = "select * from ProductionTracking where id = ?";
        return jdbcTemplate.queryForObject(sql, new ProductionTrackingRowMapper(), id);
    }

    public List<ProductionTracking> getAllProductionTracking() {
        // 添加 ORDER BY 子句，根据 audit_status 排序，未审核的在前
        String sql = "select * from ProductionTracking ORDER BY CASE WHEN audit_status = '未审核' THEN 0 ELSE 1 END";
        return jdbcTemplate.query(sql, new ProductionTrackingRowMapper());
    }

    public List<ProductionTracking> searchProductionTracking(String productName, String department, Integer managerId, String auditStatus, String batchNumber, Pageable pageable) {
        String sql = "SELECT * FROM ProductionTracking where 1 = 1";
        List<Object> params = new ArrayList<Object>();
        if (productName != null && !productName.isEmpty()) {
            sql += " AND product_name LIKE ?";
            params.add("%" + productName + "%");
        }
        if (department != null && !department.isEmpty()) {
            sql += " AND department = ?";
            params.add(department);
        }
        if (managerId != null) {
            sql += " AND manager_id = ?";
            params.add(managerId);
        }
        if (auditStatus != null && !auditStatus.isEmpty()) {
            sql += " AND audit_status = ?";
            params.add(auditStatus);
        }
        if (batchNumber != null && !batchNumber.isEmpty()) {
            sql += " AND batch_number = ?";
            params.add(batchNumber);
        }
        // 添加 ORDER BY 子句，根据 audit_status 排序，未审核的在前
        sql += " ORDER BY CASE WHEN audit_status = '未审核' THEN 0 ELSE 1 END";
        sql += " LIMIT ? OFFSET ?";
        params.add(pageable.getPageSize());
        params.add(pageable.getOffset());
        return jdbcTemplate.query(sql, new ProductionTrackingRowMapper(), params.toArray());
    }

    public int countSearchProductionTracking(String productName, String department, Integer managerId, String auditStatus, String batchNumber) {
        String sql = "SELECT count(*) FROM ProductionTracking where 1 = 1";
        List<Object> params = new ArrayList<>();
        if (productName != null && !productName.isEmpty()) {
            sql += " AND product_name LIKE ?";
            params.add("%" + productName + "%");
        }
        if (department != null && !department.isEmpty()) {
            sql += " AND department = ?";
            params.add(department);
        }
        if (managerId != null) {
            sql += " AND manager_id = ?";
            params.add(managerId);
        }
        if (auditStatus != null && !auditStatus.isEmpty()) {
            sql += " AND audit_status = ?";
            params.add(auditStatus);
        }
        if (batchNumber != null && !batchNumber.isEmpty()) {
            sql += " AND batch_number = ?";
            params.add(batchNumber);
        }
        return jdbcTemplate.queryForObject(sql, Integer.class, params.toArray());
    }

    private static class ProductionTrackingRowMapper implements RowMapper<ProductionTracking> {
        @Override
        public ProductionTracking mapRow(ResultSet rs, int rowNum) throws SQLException {
            ProductionTracking obj = new ProductionTracking();
            obj.setId(rs.getInt("id"));
            obj.setProduct_name(rs.getString("product_name"));
            obj.setPlanned_quantity(rs.getInt("planned_quantity"));
            obj.setDepartment(rs.getString("department"));
            obj.setManager_id(rs.getInt("manager_id"));
            obj.setCompleted_quantity(rs.getInt("completed_quantity"));
            obj.setAudit_status(rs.getString("audit_status"));
            obj.setPlan_id(rs.getInt("plan_id"));
            obj.setProduct_id(rs.getInt("product_id"));
            // 处理 staff_id 为 null 的情况
            if (rs.getObject("staff_id") == null) {
                obj.setStaff_id(null);
            } else {
                obj.setStaff_id(rs.getInt("staff_id"));
            }
            obj.setBatch_number(rs.getString("batch_number"));
            obj.setRemarks(rs.getString("remarks"));
            return obj;
        }
    }

    public boolean existsByPlanId(int plan_id) {
        String sql = "SELECT COUNT(*) FROM ProductionTracking WHERE plan_id = ?";
        int count = jdbcTemplate.queryForObject(sql, Integer.class, plan_id);
        return count > 0;
    }

    /**
     * 统计审核通过的信息数
     * @return 审核通过的信息数
     */
    public int countApprovedProductionTracking() {
        String sql = "SELECT COUNT(*) FROM ProductionTracking WHERE audit_status = '已审核'";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    /**
     * 统计生产跟踪的总信息数
     * @return 生产跟踪的总信息数
     */
    public int countAllProductionTracking() {
        String sql = "SELECT COUNT(*) FROM ProductionTracking";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }
}